using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Core;
using IglooCoder.Commons.WcfNhibernate;
using NHibernate;
using NHibernate.Linq;
using NHibernate.Transform;
using WcfService1.DTO;

namespace Infrastucture
{
    public class SalesInvoiceRepository : BaseNHibernateRepository<SalesInvoice>
    {
        public SalesInvoiceRepository(ISessionStorage sessionStorage)
            : base(sessionStorage)
        {
        }

        public string GetMaxNumber(string code, string kodeAwal)
        {
            string res = string.Empty;
            var lenKode = kodeAwal.Length + 11 + code.Length;
            var query = Session.CreateSQLQuery("Select Max(SINo) as Number from SalesInvoice  where SINo like'" + kodeAwal + "%'  AND SINo like'%" + code + "%'  AND Len(SINo)=" + lenKode).AddScalar("Number", NHibernateUtil.String);
            var objRes = query.List()[0];
            if (objRes != null) res = objRes.ToString();
            return res;
        }


        public  new  IList<SalesInvoiceDTO> GetDataPaged(int page, int pageSize, string sortProperty, string sortType)
        {
            var Results = new List<SalesInvoiceDTO>();
            using (ITransaction tx = Session.BeginTransaction())
            {

                StringBuilder sb = new StringBuilder();
                sb.Append("Select ps.PSNo,si.InvoiceDate,c.CustomerName,s.NamaDepan,si.Keterangan,si.SINo,so.SONo,si.Status,do.DONo,si.Term,si.CurrencyCode,si.Rate, ");
                sb.Append(
                    "(Select Sum(sid1.TotalDetail) FROM SalesInvoiceDetail sid1 WHERE SID1.SINo=si.SINo) + si.Ppn  AS Total,");
                sb.Append("CodeDepartemen as Departemen ");
                sb.Append("from SalesInvoice si  ");
                sb.Append("Left Join CostCenter cc ON cc.CostCenterID = si.CostCenterId ");
                sb.Append("LEFT JOIN DeliveryOrder do ON do.DONo = si.DONo ");
                sb.Append("LEFT JOIN SalesOrder so ON do.SONo = so.SONO ");
                sb.Append("LEFT JOIN PreSalesOrder ps on ps.PSNO=so.PSNO  ");
                sb.Append("LEFT JOIN Customer c on c.CustomerId=si.CustomerId  ");
                sb.Append("LEFT JOIN Salesman s on si.KodeSalesman=s.KodeSalesman  ");
                sb.AppendFormat("Order By {0} {1} ", sortProperty, sortType);

                var query = Session.CreateSQLQuery(sb.ToString())
                                   .SetFirstResult((page - 1) * pageSize).SetMaxResults(pageSize)
                                   .SetResultTransformer(Transformers.AliasToBean(typeof(SalesInvoiceDTO)))
                                   ;

                var count = Session.CreateSQLQuery("Select Count(*) as recCount from SalesInvoice").AddScalar("recCount", NHibernateUtil.Int64);
                Results = query.List<SalesInvoiceDTO>().ToList();

                RecordCount = (long)((IList)count.List())[0];
                tx.Commit();
            }

            //RecordCount = Results.TotalCount;
            return Results;
        }


        public new  IList<SalesInvoiceDTO> GetDataPaged(int page, int pageSize)
        {
            var Results = new List<SalesInvoiceDTO>();
            using (ITransaction tx = Session.BeginTransaction())
            {

                var sb = new StringBuilder();
                sb.Append("Select ps.PSNo,si.InvoiceDate,c.CustomerName,s.NamaDepan,si.Keterangan,si.SINo,so.SONo,si.Status,do.DONo,si.Term,si.CurrencyCode,si.Rate, ");
                sb.Append(
                    "(Select Sum(sid1.TotalDetail) FROM SalesInvoiceDetail sid1 WHERE SID1.SINo=si.SINo) + si.Ppn  AS Total,");
                sb.Append("CodeDePartemen as Departemen ");
                sb.Append("from SalesInvoice si  ");
                sb.Append("Left Join CostCenter cc ON cc.CostCenterID = si.CostCenterId ");
                sb.Append("LEFT JOIN DeliveryOrder do ON do.DONo = si.DONo ");
                sb.Append("LEFT JOIN SalesOrder so ON do.SONo = so.SONO ");
                sb.Append("LEFT JOIN PreSalesOrder ps on ps.PSNO=so.PSNO  ");
                sb.Append("LEFT JOIN Customer c on c.CustomerId=si.CustomerId  ");
                sb.Append("LEFT JOIN Salesman s on si.KodeSalesman=s.KodeSalesman  ");

                var query = Session.CreateSQLQuery(sb.ToString())
                                   .SetFirstResult((page - 1) * pageSize).SetMaxResults(pageSize)
                                   .SetResultTransformer(Transformers.AliasToBean(typeof(SalesInvoiceDTO)))
                                   ;

                var count = Session.CreateSQLQuery("Select Count(*) as recCount from SalesInvoice").AddScalar("recCount", NHibernateUtil.Int64);

                Results = query.List<SalesInvoiceDTO>().ToList();

                RecordCount = (long)((IList)count.List())[0];
                tx.Commit();
            }

            //RecordCount = Results.TotalCount;
            return Results;
        }


        public  IList<SalesInvoice> GetDataBelumLunas()
        {
            var Results = new List<SalesInvoice>();
            using (ITransaction tx = Session.BeginTransaction())
            {

             var query=  Session.CreateCriteria(typeof(SalesInvoice))
                        .SetFetchMode("SalesInvoiceDetails", FetchMode.Join)
                        .SetResultTransformer(new DistinctRootEntityResultTransformer());
                Results = query.List<SalesInvoice>().ToList();
                tx.Commit();
            }

            //RecordCount = Results.TotalCount;
            return Results;
        }

        public new IList<SalesInvoiceDTO> GetDataPaged(Dictionary<String, Object> criteria, string expression, int page, int pageSize)
        {
            IList<SalesInvoiceDTO> Results = new List<SalesInvoiceDTO>();
            using (var tx = Session.BeginTransaction())
            {

                StringBuilder sb2 = new StringBuilder();
                int i = 0;
                var filter = new List<string>();
                foreach (var criterion in criteria)
                    if (!typeof(SalesInvoice).GetProperties().Any(x => x.Name == criterion.Key)) filter.Add(criterion.Key);

                filter.ForEach((x) =>
                {
                    criteria.Remove(x);
                }
                    );

                if (criteria.Count > 0) sb2.Append(" Where ");

                foreach (var criterion in criteria)
                {
                    if (criterion.Value == null) continue;
                    if (criterion.Value.GetType() == typeof(string))
                        sb2.AppendFormat(" si." + criterion.Key + " like'%{0}%' ", criterion.Value.ToString());
                    else
                        sb2.AppendFormat(" si." + criterion.Key + "='{0}' ", criterion.Value);

                    i += 1;
                    if (i < criteria.Count)
                        sb2.Append(expression);
                }

                sb2.Replace("Currency", "CurrencyCode");
                sb2.Replace("Salesman", "KodeSalesman");
                sb2.Replace("DeliveryOrder", "DONo");
                sb2.Replace("Customer", "CustomerId");

                var sb = new StringBuilder();
                sb.Append("Select ps.PSNo,si.InvoiceDate,c.CustomerName,s.NamaDepan,si.Keterangan,si.SINo,so.SONo,si.Status,do.DONo,si.Term,si.CurrencyCode,si.Rate, ");
                sb.Append(
                    "(Select Sum(sid1.TotalDetail) FROM SalesInvoiceDetail sid1 WHERE SID1.SINo=si.SINo) + si.Ppn  AS Total,");
                sb.Append("CodeDePartemen as Departemen ");
                sb.Append("from SalesInvoice si  ");
                sb.Append("Left Join CostCenter cc ON cc.CostCenterID = si.CostCenterId ");
                sb.Append("LEFT JOIN DeliveryOrder do ON do.DONo = si.DONo ");
                sb.Append("LEFT JOIN SalesOrder so ON do.SONo = so.SONO ");
                sb.Append("LEFT JOIN PreSalesOrder ps on ps.PSNO=so.PSNO  ");
                sb.Append("LEFT JOIN Customer c on c.CustomerId=si.CustomerId  ");
                sb.Append("LEFT JOIN Salesman s on si.KodeSalesman=s.KodeSalesman  ");

                var query = Session.CreateSQLQuery(sb.ToString() + sb2.ToString())
                                   .SetFirstResult((page - 1) * pageSize).SetMaxResults(pageSize)
                                   .SetResultTransformer(Transformers.AliasToBean(typeof(SalesInvoiceDTO)))
                                   ;
                var sb3 = new StringBuilder();
                sb3.Append("Select count(*) as recCount ");
                sb3.Append("from SalesInvoice si  ");
                sb3.Append("Left Join CostCenter cc ON cc.CostCenterID = si.CostCenterId ");
                sb3.Append("LEFT JOIN DeliveryOrder do ON do.DONo = si.DONo ");
                sb3.Append("LEFT JOIN SalesOrder so ON do.SONo = so.SONO ");
                sb3.Append("LEFT JOIN PreSalesOrder ps on ps.PSNO=so.PSNO  ");
                sb3.Append("LEFT JOIN Customer c on c.CustomerId=si.CustomerId  ");
                sb3.Append("LEFT JOIN Salesman s on si.KodeSalesman=s.KodeSalesman  ");
                var count = Session.CreateSQLQuery(sb3.ToString() + sb2.ToString()).AddScalar("recCount", NHibernateUtil.Int64);
                
                Results = query.List<SalesInvoiceDTO>().ToList();

                RecordCount = (long)((IList)count.List())[0];
                tx.Commit();
            }

            return Results;
        }

        public void UpdateStatus(string SINo, int Status)
        {
            ISQLQuery command =
            Session.CreateSQLQuery("Update SalesInvoice set Status=" + Status + " Where SINo='" + SINo + "'");
            int res = command.ExecuteUpdate();
        }
    }
}